using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * 
 * Autor      : Jose Jornando de Carvalho Junior    
 * Data       : 10/04/2010 07:08:08
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
   public class CQuestionarios
   {
       private string IDT_QST ;
       private string IDT_PROP ;
       private string IDT_BENEF ;
       private string IDT_VINC_PROP ;
       private string SEQ_VINC ;
       private string IDT_TIQ ;
       private string DAT_QST ;

	   /// <sumary>
       /// Construtor da classe questionarios
       /// <sumary>
       public CQuestionarios()
       {
   
       }

	   /// <sumary>
       /// Construtor da classe Questionarios
       /// <sumary>
       public CQuestionarios( string IDT_QST, string IDT_PROP, string IDT_BENEF, string IDT_VINC_PROP, string SEQ_VINC, string IDT_TIQ, string DAT_QST )
       {
          this.IDT_QST = IDT_QST;
          this.IDT_PROP = IDT_PROP;
          this.IDT_BENEF = IDT_BENEF;
          this.IDT_VINC_PROP = IDT_VINC_PROP;
          this.SEQ_VINC = SEQ_VINC;
          this.IDT_TIQ = IDT_TIQ;
          this.DAT_QST = DAT_QST;
       }
	   
	   #region Propriedades
     	/// <sumary>
        /// Identificador do questionario
        /// <sumary>
        public string Idt_qst 
		{
		   get
		   {
		       return this.IDT_QST;
		   }
		   set
		   {
		       this.IDT_QST = value;
		   }
		}
     	/// <sumary>
        /// Identificador da propriedade
        /// <sumary>
        public string Idt_prop 
		{
		   get
		   {
		       return this.IDT_PROP;
		   }
		   set
		   {
		       this.IDT_PROP = value;
		   }
		}
     	/// <sumary>
        /// Identificador do beneficiario
        /// <sumary>
        public string Idt_benef 
		{
		   get
		   {
		       return this.IDT_BENEF;
		   }
		   set
		   {
		       this.IDT_BENEF = value;
		   }
		}
     	/// <sumary>
        /// Identificador do vinculo com a propriedade
        /// <sumary>
        public string Idt_vinc_prop 
		{
		   get
		   {
		       return this.IDT_VINC_PROP;
		   }
		   set
		   {
		       this.IDT_VINC_PROP = value;
		   }
		}
     	/// <sumary>
        /// Sequencial do vinculo com a propriedade
        /// <sumary>
        public string Seq_vinc 
		{
		   get
		   {
		       return this.SEQ_VINC;
		   }
		   set
		   {
		       this.SEQ_VINC = value;
		   }
		}
     	/// <sumary>
        /// Identificador do tipo de quetionario
        /// <sumary>
        public string Idt_tiq 
		{
		   get
		   {
		       return this.IDT_TIQ;
		   }
		   set
		   {
		       this.IDT_TIQ = value;
		   }
		}
     	/// <sumary>
        /// Data do questionario
        /// <sumary>
        public string Dat_qst 
		{
		   get
		   {
		       return this.DAT_QST;
		   }
		   set
		   {
		       this.DAT_QST = value;
		   }
		}
	    #endregion Propriedades
	
	    /// <summary>
        /// Inserindo um novo Questionarios.
        /// </summary>
        public void Inserir(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_QUESTIONARIOS
			                  ( IDT_QST, IDT_PROP, Idt_Benef, IDT_VINC_PROP, SEQ_VINC, IDT_TIQ, DAT_QST )
						   values
						      ( :IDT_QST, :IDT_PROP, :Idt_Benef, :IDT_VINC_PROP, :SEQ_VINC, :IDT_TIQ, :DAT_QST )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_QST",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_PROP",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("Idt_Benef",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_VINC_PROP",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("SEQ_VINC",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_TIQ",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("DAT_QST",OracleType.DateTime)); 
				
                command.Parameters["IDT_QST"].Value = this.IDT_QST;
                command.Parameters["IDT_PROP"].Value = this.IDT_PROP;
                command.Parameters["Idt_Benef"].Value = this.IDT_BENEF;
                command.Parameters["IDT_VINC_PROP"].Value = this.IDT_VINC_PROP;
                command.Parameters["SEQ_VINC"].Value = this.SEQ_VINC;
                command.Parameters["IDT_TIQ"].Value = this.IDT_TIQ;
                command.Parameters["DAT_QST"].Value = this.DAT_QST;
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	    /// <summary>
        /// Alterando um Questionarios.
        /// </summary>
        public void Alterar(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_QUESTIONARIOS set 
			                   IDT_PROP = :IDT_PROP, Idt_Benef = :Idt_Benef, IDT_VINC_PROP = :IDT_VINC_PROP, SEQ_VINC = :SEQ_VINC, IDT_TIQ = :IDT_TIQ, DAT_QST = :DAT_QST
						   where
						       IDT_QST = :IDT_QST ";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_QST",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_PROP",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("Idt_Benef",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_VINC_PROP",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("SEQ_VINC",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_TIQ",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("DAT_QST",OracleType.DateTime)); 
				
                command.Parameters["IDT_QST"].Value = this.IDT_QST;
                command.Parameters["IDT_PROP"].Value = this.IDT_PROP;
                command.Parameters["Idt_Benef"].Value = this.IDT_BENEF;
                command.Parameters["IDT_VINC_PROP"].Value = this.IDT_VINC_PROP;
                command.Parameters["SEQ_VINC"].Value = this.SEQ_VINC;
                command.Parameters["IDT_TIQ"].Value = this.IDT_TIQ;
                command.Parameters["DAT_QST"].Value = this.DAT_QST;
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	     /// <summary>
        /// Exclui um  Questionarios.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_QUESTIONARIOS", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login,BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_QST", DbType.Int32, this.IDT_QST));
            tab.delete();
        }

        static public DataTable ObterTodos(string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_QUESTIONARIOS order by IDT_QST ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); 
                return tbl; 
                
            }
            catch (Exception e)
            {
 
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		
		static public CQuestionarios PesquisaporCodigo(string idt_qst, string login)
        {
            CQuestionarios obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_QUESTIONARIOS 
                            WHERE IDT_QST = :IDT_QST";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.AdicionarParametro(":IDT_QST", DbType.String, idt_qst);
               
				qy.Executar();
                obj = new CQuestionarios( qy.ObterCampo("IDT_QST"), qy.ObterCampo("IDT_PROP"), qy.ObterCampo("Idt_Benef"), qy.ObterCampo("IDT_VINC_PROP"), qy.ObterCampo("SEQ_VINC"), qy.ObterCampo("IDT_TIQ"), qy.ObterCampo("DAT_QST") );
            }                                  
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CQuestionarios();
            }
			finally
            {
                qy.FecharConexao();
            }
            return obj;
        }
		
		static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_QST() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.Executar();
				return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return-1;
            }
			finally
            {
                qy.FecharConexao();
            }
        }

        static public DataTable ObterPorIDT_PROP(string idt_prop, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_QUESTIONARIOS  where IDT_PROP = :IDT_PROP order by IDT_QST ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_PROP", OracleType.Number));
                command.Parameters["IDT_PROP"].Value = idt_prop;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_BENEF(string idt_benef,string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_QUESTIONARIOS  where Idt_Benef = :Idt_Benef order by IDT_QST ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("Idt_Benef", OracleType.Number));
                command.Parameters["Idt_Benef"].Value = idt_benef;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_VINC_PROP(string idt_vinc_prop, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_QUESTIONARIOS  where IDT_VINC_PROP = :IDT_VINC_PROP order by IDT_QST ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_VINC_PROP", OracleType.Number));
                command.Parameters["IDT_VINC_PROP"].Value = idt_vinc_prop;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
        static public DataTable ObterPorSEQ_VINC(string seq_vinc, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_QUESTIONARIOS  where SEQ_VINC = :SEQ_VINC order by IDT_QST ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("SEQ_VINC", OracleType.Number));
                command.Parameters["SEQ_VINC"].Value = seq_vinc;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
        static public DataTable ObterPorIDT_TIQ(string idt_tiq, string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_QUESTIONARIOS  where IDT_TIQ = :IDT_TIQ order by IDT_QST ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_TIQ", OracleType.Number));
                command.Parameters["IDT_TIQ"].Value = idt_tiq;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
    }
}	

